﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Xml;
using System.Data.OleDb;
using System.Diagnostics;
using OisLibrary;

namespace LanguageAnalyse
{
    public partial class ExchangeExcel : Form
    {
        public ExchangeExcel()
        {
            InitializeComponent();
        }


        private void btnExecute_Click(object sender, EventArgs e)
        {
            string xmlDataPath = txtXmlData.Text.Trim();
            string filename = "";
            //裝載系統
            List<Approcess> listProcess = LoadApprocess(xmlDataPath);
            //裝載模塊
            List<Apmodel> listModel= LoadModel(xmlDataPath);

            //裝載語言
            List<LangItem> LangItemListCN = new List<LangItem>();
            List<LangItem> LangItemListTW = new List<LangItem>();
            List<LangItem> LangItemListEN = new List<LangItem>();

            PublicClass.LoadLanguage(LangItemListCN, System.IO.Path.Combine(xmlDataPath, "Languages/zh-cn.xml"));
            PublicClass.LoadLanguage(LangItemListTW, System.IO.Path.Combine(xmlDataPath, "Languages/zh-tw.xml"));
            PublicClass.LoadLanguage(LangItemListEN, System.IO.Path.Combine(xmlDataPath, "Languages/en-us.xml"));


            var lang = from langTW in LangItemListTW
                       join langEN in LangItemListEN on langTW.lgkey equals langEN.lgkey into b1
                       from langEN in b1.DefaultIfEmpty()
                       join langCN in LangItemListCN on langTW.lgkey equals langCN.lgkey into b2
                       from langCN in b2.DefaultIfEmpty()
                       join processItem in listProcess on langTW.pid equals processItem.pid into b3
                       from processItem in b3.DefaultIfEmpty()

                       join modelItem in listModel on langTW.pgid equals modelItem.pgid into b4
                       from modelItem in b4.DefaultIfEmpty()

                       select new LangItemColl
                       {
                           pid = langTW.pid,
                           pidName = processItem == null ? "" : processItem.name,
                           pgid = langTW.pgid,
                           pgidName = modelItem == null ? "" : modelItem.name,
                           key = langTW.lgkey,
                           tw = langTW.lgvalue,
                           en = langEN == null ? "" : langEN.lgvalue,
                           cn = langCN == null ? "" : langCN.lgvalue
                       };

            List<LangItemColl> listLang = lang.ToList<LangItemColl>();

            WriteToExcel(listLang);
            //KillExcelProcess(); 根據窗體標題來殺。
            MessageBox.Show("下載成功！");

        }

        private void WriteToExcel(List<LangItemColl> listLang)
        {
            object[,] data = new object[listLang.Count + 1,8];

            data[0,0]="系統ID";
            data[0, 1] = "系統名稱";
            data[0, 2] = "頁面ID";
            data[0, 3] = "頁面名稱";
            data[0, 4] = "Key";
            data[0, 5] = "繁體";
            data[0, 6] = "簡體";
            data[0, 7] = "英文";


            for(int i=0;i<listLang.Count;i++)
            {
                data[i + 1, 0] = listLang[i].pid;
                data[i + 1, 1] = listLang[i].pidName;
                data[i + 1, 2] = listLang[i].pgid;
                data[i + 1, 3] = listLang[i].pgidName;
                data[i + 1, 4] = listLang[i].key;
                data[i + 1, 5] = listLang[i].tw;
                data[i + 1, 6] = listLang[i].cn;
                data[i + 1, 7] = listLang[i].en;
            }

            ExcelOpt.TableToExcel(data, txtExcel.Text,8);

        }


        private List<Approcess> LoadApprocess(string xmlDataPath)
        {
             List<Approcess> listProcess = new List<Approcess>();
            string filename = System.IO.Path.Combine(xmlDataPath, "approcess.xml");

            XmlDocument xmlDoc = new XmlDocument();
            xmlDoc.Load(filename);
            for(int i=1;i<xmlDoc.ChildNodes[1].ChildNodes.Count;i++)
            {
                XmlNode row = xmlDoc.ChildNodes[1].ChildNodes[i];
                string pid= row.SelectSingleNode("pid").InnerText;
                string name = row.SelectSingleNode("name").InnerText;
                listProcess.Add(new Approcess { pid = pid, name = name });
            }
            return listProcess;
        }

        private List<Apmodel> LoadModel(string xmlDataPath)
        {
            List<Apmodel> listModel = new List<Apmodel>();
            string filename = System.IO.Path.Combine(xmlDataPath, "apmodel.xml");

            XmlDocument xmlDoc = new XmlDocument();
            xmlDoc.Load(filename);
            for (int i = 1; i < xmlDoc.ChildNodes[1].ChildNodes.Count; i++)
            {
                XmlNode row = xmlDoc.ChildNodes[1].ChildNodes[i];
                string pid = row.SelectSingleNode("pgid").InnerText;
                string name = row.SelectSingleNode("name").InnerText;
                listModel.Add(new Apmodel { pgid = pid, name = name });
            }
            return listModel;
        }

        private void ExchangeExcel_Load(object sender, EventArgs e)
        {
        }




        private void btnUpload_Click(object sender, EventArgs e)
        {
            string excelFile=txtExcel.Text.Trim();


            string excelConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFile + ";Extended Properties=Excel 8.0";
            string excelCmdstr = string.Format("select * from [Sheet1$]");

            string Error = "";

            try
            {
                OleDbConnection oleDbConnection = new OleDbConnection(excelConnect);
                oleDbConnection.Open();

                OleDbCommand excelCmd = new OleDbCommand(excelCmdstr, oleDbConnection);
                OleDbDataAdapter excelDa = new OleDbDataAdapter(excelCmd);

                DataTable dt = new DataTable();
                excelDa.Fill(dt);

                dt.PrimaryKey = new  DataColumn[]{ dt.Columns["Key"]};


                updaeLanguage(dt, 5, "Languages/zh-tw.xml");
                updaeLanguage(dt, 6, "Languages/zh-cn.xml");
                updaeLanguage(dt, 7, "Languages/en-us.xml");


                MessageBox.Show("更新成功！");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

        }

        private void updaeLanguage(DataTable dt, int langColumnIndex, string fileName)
        {
            fileName = System.IO.Path.Combine(txtXmlData.Text, fileName);
            

            XmlDocument xmlDoc = new XmlDocument();
            xmlDoc.Load(fileName);

            XmlNode root = xmlDoc.DocumentElement;
            XmlNodeList list1 = root.SelectNodes("aplanguageb");

            foreach (XmlNode node1 in list1)
            {
                string key = node1.SelectSingleNode("lgkey").InnerText;
                node1.SelectSingleNode("lgvalue").InnerText = getValueFromDT(dt, key, langColumnIndex);
            }

            xmlDoc.Save(fileName);

        }

        private string getValueFromDT(DataTable dt, string key, int langColumnIndex)
        {
            return dt.Rows.Find(key)[langColumnIndex].ToString();
        }


    }


    public class Lang
    {
        public string lgkey;//>FD1_WMC0101bg_codeweb</lgkey>
        public string lgvalueCN  ;// >網站別</lgvalue>
        public string lgvalueTW;
        public string lgvalueEN;
        public string pid; //>WMC</pid>
        public string pgid;//>WMC0101</pgid>
        public string pidName;
        public string pgidName;
    }

    public class Approcess
    {
        public string pid;
        public string name;
    }

    public class Apmodel
    {
        public string pgid;
        public string name;
    }

    public class LangItemColl
    {
        public string pid;
        public string pgid;
        public string pgidName;
        public string pidName;
        public string key;
        public string tw;
        public string en;
        public string cn;
    }
}
